
-- ** 创建者   : suning
-- ** 创建日期 : 20230306
-- ** 功能描述 ：城市时效明细表
-- **************************************************************************
-- **************************** 修改日志 ************************************
-- **************************************************************************
-- ** 依赖表：
-- **************************************************************************
-- *******************  CURRENT_VERSION ： V.1.1  ***************************
drop table jms_dm.dm_route_whole_shift_base_dt;
create external table if not exists jms_dm.dm_route_whole_shift_base_dt(
     in_from_code                                  string                  comment'始发编码'
    ,update_date                                   string                  comment'更新日期'
    ,in_from_provider_code                         string                  comment'始发省份编码'
    ,in_from_city_code                             string                  comment'始发市编码'
    ,in_from_area_code                             string                  comment'始发区县编码'
    ,out_to_provider_code                          string                  comment'目的省份'
    ,out_to_city_code                              string                  comment'目的市'
    ,out_to_area_code                              string                  comment'目的区县'
    ,out_to_code                                   string                  comment'目的网点编码'
    ,in_branch_id                                  string                  comment'集货id'
    ,main_id                                       string                  comment'干线全程id'
    ,out_branch_id                                 string                  comment'散货id'
    ,in_collect_code                               string                  comment'始发集散点编码'
    ,in_to_code                                    string                  comment'始发中心编码'
    ,start_center_code                             string                  comment'始发转运中心编码'
    ,search_type                                   int                     comment'搜索类型'
    ,start_network_code                            string                  comment'始发网点编码'
    ,in_from_regional_code                         string                  comment'始发大区编码'
    ,in_from_regional_desc                         string                  comment'始发大区'
    ,in_from_financial_center_code                 string                  comment'始发代理区编码'
    ,in_from_financial_center_desc                 string                  comment'始发代理区'
    ,in_from_provider_desc                         string                  comment'始发省份'
    ,in_from_city_desc                             string                  comment'始发市'
    ,in_from_area_desc                             string                  comment'始发区县'
    ,in_collect_name                               string                  comment'始发集散点'
    ,in_from_name                                  string                  comment'始发网点'
    ,in_to_name                                    string                  comment'始发转运中心'
    ,in_edge_latest_warehousing                    string                  comment'网点规划最晚入仓时间'
    ,in_edge_planned_departure                     string                  comment'网点交件规划发车时间'
    ,in_edge_planned_arrival                       string                  comment'网点规划交件到达时间'
    ,in_edge2_planned_departure                    string                  comment'集散点规划发车时间'
    ,in_edge2_planned_arrival                      string                  comment'集散点规划到达时间'
    ,branch_in_span_days                           int                     comment'交件耗时（天）'
    ,branch_in_total_time                          int                     comment'交件耗时（分）'
    ,is_main_route                                 string                  comment'是否是主路由'
    ,transfer_type                                 string                  comment'中转类型'
    ,num_of_transfer                               string                  comment'中转次数'
    ,whole_route                                   string                  comment'路由环节'
    ,e1_center_flow                                string                  comment'流向1'
    ,e1_line_code                                  string                  comment'流向1线路名称'
    ,e1_planned_departure                          string                  comment'流向1规划发出时间'
    ,e1_runtime                                    int                     comment'流向1规划运行时长'
    ,e1_planned_arrival                            string                  comment'流向1规划到达时间'
    ,e1_e2_stop_time                               string                  comment'转-转衔接时长e1-e2'
    ,e2_center_flow                                string                  comment'流向2'
    ,e2_line_code                                  string                  comment'流向2线路名称'
    ,e2_planned_departure                          string                  comment'流向2规划发出时间'
    ,e2_runtime                                    int                     comment'流向2规划运行时长'
    ,e2_planned_arrival                            string                  comment'流向2规划到达时间'
    ,e2_e3_stop_time                               string                  comment'转-转衔接时长e2-e3'
    ,e3_center_flow                                string                  comment'流向3'
    ,e3_line_code                                  string                  comment'流向3线路名称'
    ,e3_planned_departure                          string                  comment'流向3规划发出时间'
    ,e3_runtime                                    int                     comment'流向3规划运行时长'
    ,e3_planned_arrival                            string                  comment'流向3规划到达时间'
    ,e3_e4_stop_time                               string                  comment'转-转衔接时长e3-e4'
    ,e4_center_flow                                string                  comment'流向4线路名称'
    ,e4_line_code                                  string                  comment'流向4'
    ,e4_planned_departure                          string                  comment'流向4规划发出时间'
    ,e4_runtime                                    int                     comment'流向4规划运行时长'
    ,e4_planned_arrival                            string                  comment'流向4规划到达时间'
    ,e4_e5_stop_time                               string                  comment'转-转衔接时长e4-e5'
    ,e5_center_flow                                string                  comment'流向5'
    ,e5_line_code                                  string                  comment'流向5线路名称'
    ,e5_planned_departure                          string                  comment'流向5规划发出时间'
    ,e5_runtime                                    int                     comment'流向5规划运行时长'
    ,e5_planned_arrival                            string                  comment'流向5规划到达时间'
    ,e5_e6_stop_time                               string                  comment'转-转衔接时长e2-e3'
    ,e6_center_flow                                string                  comment'流向6'
    ,e6_line_code                                  string                  comment'流向6线路名称'
    ,e6_planned_departure                          string                  comment'流向6规划发出时间'
    ,e6_runtime                                    int                     comment'流向6规划运行时长'
    ,e6_planned_arrival                            string                  comment'流向6规划到达时间'
    ,e6_e7_stop_time                               string                  comment'转-转衔接时长e6-e7'
    ,e1_end_code                                   string                  comment'经停点1'
    ,e2_end_code                                   string                  comment'经停点2'
    ,e3_end_code                                   string                  comment'经停点3'
    ,e4_end_code                                   string                  comment'经停点4'
    ,e5_end_code                                   string                  comment'经停点5'
    ,e6_end_code                                   string                  comment'经停点6'
    ,transfer_total_span_days                      string                  comment'中转跨天'
    ,transfer_total_time_use                       string                  comment'中转耗时（分）'
    ,main_total_span_days                          string                  comment'干线运输跨天'
    ,main_total_time_use                           string                  comment'干线运输耗时（分）'
    ,end_center                                    string                  comment'目的中心'
    ,end_center_code                               string                  comment'目的中心编码'
    ,start_center                                  string                  comment'始发中心'
    ,out_from_code                                 string                  comment'目的转运中心编码'
    ,out_collect_code                              string                  comment'目的集散点编码'
    ,out_to_regional_desc                          string                  comment'目的大区'
    ,out_to_regional_code                          string                  comment'目的大区编码'
    ,out_to_financial_center_desc                  string                  comment'目的代理区'
    ,out_to_financial_center_code                  string                  comment'目的代理区编码'
    ,out_to_provider_desc                          string                  comment'目的省份'
    ,out_to_city_desc                              string                  comment'目的市'
    ,out_to_area_desc                              string                  comment'目的区县'
    ,out_collect_name                              string                  comment'目的集散点'
    ,out_to_name                                   string                  comment'目的网点'
    ,out_from_name                                 string                  comment'目的转运中心'
    ,out_has_collect                               string                  comment'线路是否有集散中心'
    ,out_edge_planned_departure                    string                  comment'目的转运中心规划发车时间'
    ,out_edge_planned_arrival_time                 string                  comment'集散点规划到车时间'
    ,out_edge2_planned_departure                   string                  comment'集散点规划发车时间'
    ,out_edge2_planned_arrival_time                string                  comment'网点规划到车时间'
    ,out_edge2_latest_warehouse_time               string                  comment'集散规划出仓时间'
    ,out_edge_latest_warehouse_time                string                  comment'中心规划出仓时间'
    ,out_edge_span_days_sign                       string                  comment'集散派送加时'
    ,out_edge2_span_days_sign                      string                  comment'网点派送加时'
    ,out_edge_deadline_sign_time                   string                  comment'集散点规划签收'
    ,out_edge2_deadline_sign_time                  string                  comment'网点规划签收'
    ,branch_out_span_days                          int                     comment'派件耗时（天）'
    ,branch_out_total_time                         int                     comment'派件耗时（分）'
    ,effective_date                                string                  comment'生效日期'
    ,expiration_date                               string                  comment'失效日期'
    ,branch_in_center_stop_time                    int                     comment'交转衔接时长'
    ,center_branch_out_stop_time                   int                     comment'转散衔接时长'
    ,branch_in_center_span_days                    int                     comment'交转衔接跨天'
    ,center_branch_out_span_days                   int                     comment'转散衔接跨天'
    ,is_circuitous                                 int                     comment'是否迂回'
    ,out_edge_span_days_arrive                     int                     comment'集散跨越天数_到车的数据'
    ,out_edge2_span_days_arrive                    int                     comment'网点跨越天数_到车的数据'
    ,in_nodes                                      int                     comment'集货节点数'
    ,out_nodes                                     int                     comment'散货节点数'
    ,total_days_use                                string                  comment'全程时效(X日达)'
    ,total_time_use                                int                     comment'全程耗时(小时)'
    ,total_days_t                                  string                  comment'全程T+时效(天)'
    ,total_nodes                                   int                     comment'总节点数'
    ,working_days                                  string                  comment'适用工作日'
    ,extra_in_collect_code                         string                  comment'始发集散点2编码'
    ,extra_in_collect_name                         string                  comment'始发集散点2'
    ,extra_in_line_name                            string                  comment'始发集散点2线路名称'
    ,extra_in_latest_warehousing                   string                  comment'始发集散点2规划最晚到车时间'
    ,extra_in_planned_departure                    string                  comment'始发集散点2规划发车时间'
    ,extra_in_planned_arrival                      string                  comment'始发集散点2规划到车时间'
    ,extra_out_collect_code                        string                  comment'目的集散点2编码'
    ,extra_out_collect_name                        string                  comment'目的集散点2'
    ,extra_out_line_name                           string                  comment'目的集散点2线路名称'
    ,extra_out_latest_warehousing                  string                  comment'目的集散点2最晚到车时间'
    ,extra_out_planned_departure                   string                  comment'目的集散点2规划发车时间'
    ,extra_out_planned_arrival                     string                  comment'目的集散点2规划到车时间'
    ,in_network_line_name                          string                  comment'始发网点线路名'
    ,in_collect_line_name                          string                  comment'始发集散线路名'
    ,out_network_line_name                         string                  comment'目的网点线路名'
    ,out_collect_line_name                         string                  comment'目的集散线路名'
    ,town_plus_time                                string                  comment'交件乡镇加时'
    ,branch_in_ship_time                           string                  comment'交件运输耗时（分）'
    ,branch_in_ship_span_days                      string                  comment'交件运输耗时（天）'
    ,branch_out_ship_time                          string                  comment'派件运输耗时（分）'
    ,branch_out_ship_span_days                     string                  comment'派件运输耗时（天）'
    ,all_line_name                                 string                  comment'中转路由'
    ,out_town_plus_time                            int                     comment'派件乡镇加时'
    ,in_edge_run_time                              int                     comment'集货第一段耗时'
    ,in_edge_span_days                             int                     comment'集货第一段跨天'
    ,in_edge2_run_time                             int                     comment'集货第二段耗时'
    ,in_edge2_span_days                            int                     comment'集货第二段跨天'
    ,in_edge3_run_time                             int                     comment'集货第三段耗时'
    ,in_edge3_span_days                            int                     comment'集货第三段跨天'
    ,out_edge_run_time                             int                     comment'散货第一段耗时'
    ,out_edge_span_days                            int                     comment'散货第一段跨天'
    ,out_edge2_run_time                            int                     comment'散货第二段耗时'
    ,out_edge2_span_days                           int                     comment'散货第二段跨天'
    ,out_edge3_run_time                            int                     comment'散货第三段耗时'
    ,out_edge3_span_days                           int                     comment'散货第三段跨天'
    ,e1_span_days                                  int                     comment'流向1规划跨天'
    ,e2_span_days                                  int                     comment'流向2规划跨天'
    ,e3_span_days                                  int                     comment'流向3规划跨天'
    ,e4_span_days                                  int                     comment'流向4规划跨天'
    ,e5_span_days                                  int                     comment'流向5规划跨天'
    ,e6_span_days                                  int                     comment'流向6规划跨天'
    ,delivery_time                                 string                  comment'"派件耗时,目的网点到车时间与签收时间的总耗时"'
    ,route_flow                                    string                  comment'始发地-目的地流向'
    ,in_edge2_latest_warehousing                   string                  comment'集散规划最晚到车时间'
    ,e1_latest_warehousing                         string                  comment'流向1规划最晚到车时间'
    ,e2_latest_warehousing                         string                  comment'流向2规划最晚到车时间'
    ,e3_latest_warehousing                         string                  comment'流向3规划最晚到车时间'
    ,e4_latest_arrival_time                        string                  comment'流向4规划最晚到车时间'
    ,e5_latest_arrival_time                        string                  comment'流向5规划最晚到车时间'
    ,e6_latest_arrival_time                        string                  comment'流向6规划最晚到车时间'
    ,start_taking_shift                            string                  comment'始发网点揽收班次'
    ,end_send_shift                                string                  comment'目的网点派仓班次'
    ,warehouse_end_time                            string                  comment'派仓派件结束时间'
    ,reserve_1                                     string                  comment'reserve_1'
    ,reserve_2                                     string                  comment'reserve_2'
    ,route_contain_main_line                       int                     comment'是否存在干线的线路1:是0:否'
    ,direct_route_type                             int                     comment'"直发线路类型,1:网点-中心2:集散-中心3:中心-集散4:中心-网点0:没有来自干线的线路"'
    ,in_line_day                                   int                     comment'收件班次-支线衔接跨天'
    ,out_line_day                                  int                     comment'派件班次-支线衔接跨天'
    ,in_ship_span_days                             int                     comment'"交件运输跨天,网点的规划发车-始发中心到车时间的跨天数(不含集散停留时长)"'
    ,in_manage_region_code                         string                  comment'始发管理大区编码'
    ,in_manage_region_name                         string                  comment'始发管理大区名称'
    ,out_manage_region_code                        string                  comment'目的管理大区编码'
    ,out_manage_region_name                        string                  comment'目的管理大区名称'
    ,latest_warehousing_time_quantum               string                  comment'揽收时间段'
    ,lasted_line_name                              string                  comment'中转环节最后一个车线'
    ,platform_code_cn                              string                  comment'菜鸟平台编码'
    ,platform_cn                                   string                  comment'菜鸟平台名称'
    ,effective_times_cn                            bigint                  comment'菜鸟平台时效'
    ,platform_code_zt                              string                  comment'中通平台编码'
    ,platform_zt                                   string                  comment'中通平台名称'
    ,effective_times_zt                            bigint                  comment'中通平台时效'
    ,platform_code_sf                              string                  comment'顺丰平台编码'
    ,platform_sf                                   string                  comment'顺丰平台名称'
    ,effective_times_sf                            bigint                  comment'顺丰平台时效'
    ,platform_code_yt                              string                  comment'圆通平台编码'
    ,platform_yt                                   string                  comment'圆通平台名称'
    ,effective_times_yt                            bigint                  comment'圆通平台时效'
    ,platform_code_yd                              string                  comment'韵达平台编码'
    ,platform_yd                                   string                  comment'韵达平台名称'
    ,effective_times_yd                            bigint                  comment'韵达平台时效'
    ,platform_code_thd                             string                  comment'桃花岛平台编码'
    ,platform_thd                                  string                  comment'桃花岛平台名称'
    ,effective_times_thd                           bigint                  comment'桃花岛平台时效'
    ,platform_code_zjs                             string                  comment'紫金山平台编码'
    ,platform_zjs                                  string                  comment'紫金山平台名称'
    ,effective_times_zjs                           bigint                  comment'紫金山平台时效'
    ,if_cn_cp                                      bigint                  comment'是否持平菜鸟'
    ,if_zt_cp                                      bigint                  comment'是否持平中通'
    ,if_sf_cp                                      bigint                  comment'是否持平顺丰'
    ,if_yt_cp                                      bigint                  comment'是否持平圆通'
    ,if_yd_cp                                      bigint                  comment'是否持平韵达'
    ,if_thd_cp                                     bigint                  comment'是否持平桃花岛'
    ,if_zjs_cp                                     bigint                  comment'是否持平紫金山'
    ,if_cn_my                                      bigint                  comment'是否慢于菜鸟'
    ,if_zt_my                                      bigint                  comment'是否慢于中通'
    ,if_sf_my                                      bigint                  comment'是否慢于顺丰'
    ,if_yt_my                                      bigint                  comment'是否慢于圆通'
    ,if_yd_my                                      bigint                  comment'是否慢于韵达'
    ,if_thd_my                                     bigint                  comment'是否慢于桃花岛'
    ,if_zjs_my                                     bigint                  comment'是否慢于紫金山'
    ,if_city                                       bigint                  comment'同城件'
) comment '规划平台-静态路由-城市时效班次明细表'
partitioned by (dt string comment '分区日期')
stored as parquet
location '/dw/hive/jms_dm.db/external/dm_route_whole_shift_base_dt'
tblproperties (
'discover.partitions'='false',
'parquet.column.index.access'='true'
);

